﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml;
using System.Configuration;

namespace ReportWebService
{
    /// <summary>
    /// Dit is de webservice die een xml teruggeeft
    /// </summary>
    [WebService(Namespace = "http://microsoft.com/webservices/", Description = "This XML Web service contains information about the Adventure Works database.")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class ReportService : AbstractWebservice.AbstractService
    {
        SqlConnection conn = new SqlConnection();
        SqlDataAdapter adp = new SqlDataAdapter();
        
        string sQuery = "SELECT Production.Product.Name " +
            "FROM Sales.Customer " +
            "INNER JOIN Sales.SalesOrderHeader " +
            "ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID " +
            "CROSS JOIN Production.Product " +
            "WHERE (Sales.Customer.CustomerID = 17)";

        private string getQueryProductsPerCustomer(int level, int customerID)
        {
            string query;
            switch (level)
            {
                case 1:
                    query = "SELECT OH.CustomerID, P.Name, COUNT(P.ProductID) AS 'Amount Products', SUM(OD.UnitPrice) AS 'Total price', AVG(OD.UnitPrice) AS 'Average Unit Price' " +
                        "FROM Sales.SalesOrderHeader AS OH " +
                        "INNER JOIN Sales.SalesOrderDetail AS OD ON OH.SalesOrderID = OD.SalesOrderID " +
                        "INNER JOIN Production.Product AS P ON OD.ProductID = P.ProductID " +
                        "WHERE (OH.CustomerID = " + customerID + ") " +
                        "GROUP BY OH.CustomerID, P.Name " +
                        "ORDER BY P.Name";
                    break;
                case 2:
                    query = "SELECT OH.CustomerID, P.Name, COUNT(P.ProductID) AS 'Amount Products', SUM(OD.UnitPrice) AS 'Total price', AVG(OD.UnitPrice) AS 'Average Unit Price' " +
                        "FROM Sales.SalesOrderHeader AS OH " +
                        "INNER JOIN Sales.SalesOrderDetail AS OD ON OH.SalesOrderID = OD.SalesOrderID " +
                        "INNER JOIN Production.Product AS P ON OD.ProductID = P.ProductID " +
                        "WHERE (OH.CustomerID = " + customerID + ") " +
                        "GROUP BY OH.CustomerID, P.Name " +
                        "ORDER BY P.Name";
                    break;
                case 3:
                    query = "SELECT OH.CustomerID, P.Name, COUNT(P.ProductID) AS 'Amount Products', SUM(OD.UnitPrice) AS 'Total price', AVG(OD.UnitPrice) AS 'Average Unit Price' " +
                        "FROM Sales.SalesOrderHeader AS OH " +
                        "INNER JOIN Sales.SalesOrderDetail AS OD ON OH.SalesOrderID = OD.SalesOrderID " +
                        "INNER JOIN Production.Product AS P ON OD.ProductID = P.ProductID " +
                        "WHERE (OH.CustomerID = " + customerID + ") " +
                        "GROUP BY OH.CustomerID, P.Name " +
                        "ORDER BY P.Name";
                    break;
                default:
                    query = "";
                    break;
            }
            return query;
        }

        private string getQueryAllCustomers()
        {
            return "SELECT TOP (10) CustomerID, AccountNumber " +
            "FROM Sales.Customer " +
            "ORDER BY AccountNumber";
        }

        [WebMethod]
        public override DataSet getComponents()
        {
            DataSet results = new DataSet();
            DataSet ds = new DataSet();
            //// make sql conn and execute query
            //this.conn.ConnectionString = this.connString;
            //adp = new SqlDataAdapter(getQueryAllCustomers(), conn);
            //adp.Fill(results);
            //conn.Close();
            string connStr = ConfigurationManager.ConnectionStrings["AWCDB"].ConnectionString;

            results = DataLayerClass.DataConnection.getAllCustomers(connStr);

            // build xml
            XmlDocument xmldoc = new XmlDocument();
            XmlNode docNode;
            XmlNode componentsNode;
            XmlNode componentNode;
            XmlNode itemNode;
            XmlAttribute componentAttributeType;
            XmlAttribute componentAttributeLabel;
            XmlAttribute itemAttributeName;
            XmlAttribute itemAttributeValue;
            docNode = xmldoc.CreateXmlDeclaration("1.0", "UTF-8", null);
            xmldoc.AppendChild(docNode);

            componentsNode = xmldoc.CreateElement("components");
            xmldoc.AppendChild(componentsNode);
            /*
            // Eerste component node
            componentNode = xmldoc.CreateElement("component");
            componentAttributeType = xmldoc.CreateAttribute("type");
            componentAttributeType.Value = "label";
            componentAttributeLabel = xmldoc.CreateAttribute("label");
            componentAttributeLabel.Value = "Tekstje";
            componentNode.Attributes.Append(componentAttributeType);
            componentNode.Attributes.Append(componentAttributeLabel);
            componentsNode.AppendChild(componentNode);

            // 2e comp node
            componentNode = xmldoc.CreateElement("component");
            componentAttributeType = xmldoc.CreateAttribute("type");
            componentAttributeType.Value = "string";
            componentAttributeLabel = xmldoc.CreateAttribute("label");
            componentAttributeLabel.Value = "Input string";
            componentNode.Attributes.Append(componentAttributeType);
            componentNode.Attributes.Append(componentAttributeLabel);
            componentsNode.AppendChild(componentNode);
            */
            try
            {
                // 3e comp node
                componentNode = xmldoc.CreateElement("component");
                componentAttributeType = xmldoc.CreateAttribute("type");
                componentAttributeType.Value = "list";
                componentAttributeLabel = xmldoc.CreateAttribute("label");
                componentAttributeLabel.Value = "Lijstje";
                componentNode.Attributes.Append(componentAttributeType);
                componentNode.Attributes.Append(componentAttributeLabel);

                // nu adden we de list
                foreach (DataRow row in results.Tables[0].Rows)
                {
                    itemNode = xmldoc.CreateElement("item");
                    itemAttributeName = xmldoc.CreateAttribute("name");
                    itemAttributeName.Value = row["AccountNumber"].ToString();
                    itemAttributeValue = xmldoc.CreateAttribute("value");
                    itemAttributeValue.Value = row["CustomerID"].ToString();
                    itemNode.Attributes.Append(itemAttributeName);
                    itemNode.Attributes.Append(itemAttributeValue);
                    componentNode.AppendChild(itemNode);
                }
                componentsNode.AppendChild(componentNode);
            }
            catch (Exception ex)
            {
                // alleen een lijst genereren als we geen error krijgen.
            }
            /*
            // 4e comp node
            componentNode = xmldoc.CreateElement("component");
            componentAttributeType = xmldoc.CreateAttribute("type");
            componentAttributeType.Value = "datetime";
            componentAttributeLabel = xmldoc.CreateAttribute("label");
            componentAttributeLabel.Value = "Date";
            componentNode.Attributes.Append(componentAttributeType);
            componentNode.Attributes.Append(componentAttributeLabel);
            componentsNode.AppendChild(componentNode);
            */
            // gooi de xml terug in een dataset
            ds.ReadXml(new XmlNodeReader(xmldoc));
            return ds;
        }

        [WebMethod]
        public override DataSet getDataset(DataSet Values)
        {
            DataSet results = new DataSet();
            try
            {
                int level = Int32.Parse(Values.Tables["level"].Rows[0]["value"].ToString());
                int customerID = Int32.Parse(Values.Tables["values"].Rows[0]["value"].ToString());
                string connStr = ConfigurationManager.ConnectionStrings["AWCDB"].ConnectionString;
                results = DataLayerClass.DataConnection.getProductsPerCustomer(level, customerID, connStr);
            }
            catch (Exception ex)
            {
                // gooi de exception naar het dashboard die de webservice aanroept
                // deze zal de foutmelding op de site tonen.
                throw ex;
            }
            return results;
        }

        [WebMethod]
        public DataSet getDatasetTest()
        {
            DataSet Values = new DataSet();
            Values.ReadXml(@"C:\Users\Eigenaar\Documents\result.xml");

            int level = Int32.Parse(Values.Tables["level"].Rows[0]["value"].ToString());
            int customerID = Int32.Parse(Values.Tables["values"].Rows[0]["value"].ToString());
            string connStr = ConfigurationManager.ConnectionStrings["AWCDB"].ConnectionString;
            DataSet results = DataLayerClass.DataConnection.getProductsPerCustomer(level, customerID,connStr);
            return results;
        }
    }
}
